package org.qydjk.common.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel工具类
 * @author lp
 *
 */
public class ExcelUtil {
    /**
     * 读取出filePath中的所有数据信息，存入List<Map<String,String>>
     *@param filePath excel文件的绝对路径
     */
    public static Map<String,Object> getDataFromExcel(File filePath) throws IOException {
        InputStream is = new FileInputStream(filePath);
        return getDataFromExcel(is);
    }

    /**
     * 读取出InputStream中的所有数据信息，存入List<Map<String,String>>
     * @param is
     * @return
     * @throws IOException
     */
    public static Map<String,Object> getDataFromExcel( InputStream is) throws IOException {
        Map<String,Object> objectMap = new HashMap<String,Object>();
        XSSFWorkbook wookbook = new XSSFWorkbook(is);
        int flag = 0;
        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);
        //获得表头
        Row rowHead = sheet.getRow(0);
        //判断表头是否合格  ------------------------这里看你有多少列
        int colnum = rowHead.getPhysicalNumberOfCells();
        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();
        //要获得属性
        if (0 == totalRowNum) {
            objectMap.put("msg","Excel内没有数据！");
            return objectMap;
        }
        System.out.println("数据总列数"+colnum);
        System.out.println("数据总行数"+totalRowNum);
        //获得所有数据
        List<Map<String, String>> execlDate = new ArrayList<>();
        String codeStr = ",";
        for (int i = 1;i <= totalRowNum; i++) {
            //获得第i行对象
            Map<String, String> map = new HashMap<>();
            Row row = sheet.getRow(i);
            try {
                for (int col = 0; col < colnum; col++) {
                    Cell cell = row.getCell(col);
                    System.out.print(getRightTypeCell(cell)+"-|");
                    String val = getRightTypeCell(cell).toString();
                    map.put("col_"+(col), val);
                    //卡号唯一检查
                    String test = ","+val+",";
                    if(col==0&&codeStr.indexOf(test)!=-1){
                        objectMap.put("msg","存在相同数据！");
                        return objectMap;
                    }
                }
                codeStr=codeStr+map.get("col_0")+",";
                execlDate.add(map);
                System.out.println();
            } catch (Exception e) {
                e.printStackTrace();
                objectMap.put("msg","获取单元格错误！");
                return objectMap;
            }
        }
        objectMap.put("msg","OK");
        objectMap.put("codestr",codeStr);
        objectMap.put("content",execlDate);
        return objectMap;
    }

    /**
     * @param cell 一个单元格的对象
     * @return 返回该单元格相应的类型的值
     */
    public static Object getRightTypeCell(Cell cell) {
        Object object = null;
        String type = cell.getCellTypeEnum().toString();
        //System.out.println("单元格格式————"+type);
        if("NUMERIC".equals(type)){
            // 返回数值类型的值
            Long longVal = Math.round(cell.getNumericCellValue());
            Double doubleVal = cell.getNumericCellValue();
            if(Double.parseDouble(longVal + ".0") == doubleVal){   //判断是否含有小数位.0
                object = longVal;
            }
            else{
                object = doubleVal;
            }
            DecimalFormat df = new DecimalFormat("#");    //格式化为四位小数，按自己需求选择；
            object =  String.valueOf(df.format(object));      //返回String类型
        }else{
            object = cell.getStringCellValue();
        }
        return object;
    }
}
